package other.excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @author bingo
 * @Description
 * @Date 2018/8/20
 */
public class ExportExcel {

    @Test
    public  void test2(){
        Workbook wb = new XSSFWorkbook();
        Sheet sheet1 = wb.createSheet("new sheet");
        Sheet sheet2 = wb.createSheet("second sheet");
        Row row = sheet1.createRow((short)1);
        // Create a cell and put a value in it.
        Cell cell = row.createCell(1);
        cell.setCellValue(1);
        CreationHelper createHelper = wb.getCreationHelper();
        // Or do it on one line.
        row.createCell(0).setCellValue(1.2);
        row.createCell(2).setCellValue(
                createHelper.createRichTextString("This is a string"));
        row.createCell(3).setCellValue(true);
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream("D:\\file\\other.excel\\workbook.xlsx");
            
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    /**
     *  日期格式
     *
     * */
    @Test
    public void testCreateDate(){

        Workbook wb = new SXSSFWorkbook();
        //Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(0);

        // Create a cell and put a date value in it.  The first cell is not styled
        // as a date.
        Cell cell = row.createCell(0);
        cell.setCellValue(new Date());

        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(
                createHelper.createDataFormat().getFormat("yyyy/m/d h:mm"));
        cell = row.createCell(1);
        cell.setCellValue(new Date());
        cell.setCellStyle(cellStyle);

        //you can also set date as java.util.Calendar
        cell = row.createCell(2);
        cell.setCellValue(Calendar.getInstance());
        cell.setCellStyle(cellStyle);
        Row row2 = sheet.createRow((short)2);
        row2.createCell(0).setCellValue(1.1);
        row2.createCell(1).setCellValue(new Date());
        row2.createCell(2).setCellValue(Calendar.getInstance());
        row2.createCell(3).setCellValue("a string");
        row2.createCell(4).setCellValue(true);
        //row2.createCell(5).setCellType(CellType.e);

        // Write the output to a file
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream("D:\\file\\other.excel\\workbook.xlsx");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    /**
     *  增减边框
     *  */
    @Test
    public  void border() throws IOException {
        Workbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(1);

        // Create a cell and put a value in it.
        Cell cell = row.createCell(1);
        cell.setCellValue(4);

        // Style the cell with borders all around.
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
    /**
     *  填充颜色
     * */
    @Test
    public void fileColor () throws IOException {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short) 1);

        // Aqua background
        CellStyle style = wb.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
        style.setFillPattern(CellStyle.BIG_SPOTS);
        Cell cell = row.createCell((short) 1);
        cell.setCellValue("X");
        cell.setCellStyle(style);

        // Orange "foreground", foreground being the fill foreground not the font color.
        style = wb.createCellStyle();
        style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cell = row.createCell((short) 2);
        cell.setCellValue("X");
        cell.setCellStyle(style);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
    /**
     *  合并单元格
     * */
    @Test
    public void mergeCell() throws Exception{
        Workbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");

        Row row = sheet.createRow((short) 1);
        Cell cell = row.createCell((short) 1);
        cell.setCellValue("This is a test of merging");

        sheet.addMergedRegion(new CellRangeAddress(
                1, //first row (0-based)
                1, //last row  (0-based)
                1, //first column (0-based)
                2  //last column  (0-based)
        ));

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
    @Test
    public void  testFont() throws Exception{
        Workbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(1);

        // Create a new font and alter it.
        Font font = wb.createFont();
        font.setFontHeightInPoints((short)24);
        font.setFontName("Courier New");
        font.setItalic(true);
        font.setStrikeout(true);

        // Fonts are set into a style so create a new one to use.
        CellStyle style = wb.createCellStyle();
        style.setFont(font);

        // Create a cell and put a value in it.
        Cell cell = row.createCell(1);
        cell.setCellValue("This is a test of fonts");
        cell.setCellStyle(style);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
    @Test
    public void testDrawImage() throws IOException {

        Workbook wb = new XSSFWorkbook();
        InputStream is = new FileInputStream("demo/1.png");
        byte[] bytes = IOUtils.toByteArray(is);
        int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        is.close();

        CreationHelper helper = wb.getCreationHelper();


        Sheet sheet = wb.createSheet();


        Drawing drawing = sheet.createDrawingPatriarch();


        ClientAnchor anchor = helper.createClientAnchor();

        anchor.setCol1(3);
        anchor.setRow1(2);
        Picture pict = drawing.createPicture(anchor, pictureIdx);


        pict.resize();

        //save workbook
        String file = "picture.xls";
        if(wb instanceof XSSFWorkbook) file += "x";
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);

    }
    @Test
    public void  exportExcel() throws IOException {
        List<Person> list = new ArrayList<Person>();
        for(int i=0;i<100;i++){
            list.add(new Person(i+"#","1515825058"+i,"xxxxxx"+i));
            }
        String[] data  = {"姓名","电话","地址"};
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet =workbook.createSheet("人员信息");
        CellStyle headStyle = workbook.createCellStyle();
        Font font  = workbook.createFont();
        font.setColor(Font.COLOR_NORMAL);
        Row headRow  = sheet.createRow(0);
        for(int i=0;i<data.length;i++){
            Cell cell = headRow.createCell(i);
            cell.setCellValue(data[i]);
        }

        for(int i=0;i<list.size();i++){
            Row row = sheet.createRow(i+1);
                Cell nameCell  = row.createCell(0);
                Cell numberCell = row.createCell(1);
                Cell addrCell = row.createCell(2);
                nameCell.setCellValue(list.get(i).getName());
                numberCell.setCellValue(list.get(i).getNumber());
                addrCell.setCellValue(list.get(i).getAddr());

        }
        FileOutputStream fileOutputStream  = new FileOutputStream("student.xls");
        workbook.write(fileOutputStream);



    }
}
